/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxline.clinicplus.service;

import br.com.foxline.clinicplus.entity.*;
import br.com.foxline.clinicplus.enums.*;
import br.com.foxline.clinicplus.vo.*;
import java.math.BigDecimal;
import java.net.URL;
import java.util.*;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
public class IncomeService extends GenericService<Income> {

    @EJB
    ClinicService clinicService;
    @EJB
    SystemUserService systemUserService;

    public IncomeService() {
        super(Income.class);
    }

    public BigDecimal getValueIncomeGeneric(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType, SystemUser systemUser,
            TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure,
            GroupProcedure groupProcedure, Boolean compensado, Boolean onlyCofre) {

        String sql = "Select sum(i.amountPaid) from Income i ";


        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";

        } else if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN i.diary di ";
        }

        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || typeProcedure != null
                || groupProcedure != null) {
            sql += " JOIN di.procedure p ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }

        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }

        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }
        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }

        if (dateInitial != null) {
            sql += " and ((i.faturamentoImediato = true and i.createAt BETWEEN :dateInitial AND :dateFinal)"
                    + " or (i.faturamentoImediato = false and i.compensado = true and i.dataCompensacao BETWEEN :dateInitial AND :dateFinal) "
                    + " or (i.faturamentoImediato = false and ( i.compensado = false or i.compensado is null ) "
                    + "and i.createAt BETWEEN :dateInitial AND :dateFinal)) ";
        }

        if (onlyCofre != null) {
            sql += " and i.cash is null ";
        }

        if (compensado != null && compensado) {
            sql += " and ( i.compensado = " + compensado + " )";
        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }
        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        System.err.println(sql);

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);

            if (gc1.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);
            }

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                    gc2.set(GregorianCalendar.MINUTE, 59);
                    gc2.set(GregorianCalendar.SECOND, 0);
                }
            }
            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();
            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }
        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }
        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }



        return (BigDecimal) createQuery.getSingleResult();
    }

    public List<Income> findIncomeGeneric(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType,
            SystemUser systemUser, TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure,
            GroupProcedure groupProcedure, Boolean compensado, Boolean onlyCofre) {

        String sql = "Select distinct(i) from Income i ";

        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";

        } else if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN i.diary di ";
        }

        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN di.procedure p ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }
        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }
        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }

        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }

        if (dateInitial != null) {
            sql += " and ((i.faturamentoImediato = true and i.createAt BETWEEN :dateInitial AND :dateFinal)"
                    + " or (i.faturamentoImediato = false and i.compensado = true and i.dataCompensacao BETWEEN :dateInitial AND :dateFinal) "
                    + " or (i.faturamentoImediato = false and ( i.compensado = false or i.compensado is null ) "
                    + "and i.createAt BETWEEN :dateInitial AND :dateFinal)) ";
        }

        if (onlyCofre != null) {
            sql += " and i.cash is null ";
        }

        if (compensado != null && compensado) {
            sql += " and i.compensado = " + compensado + " ";
        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }

        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        sql += " ORDER BY i.createAt ASC";

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);

            if (gc1.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);
            }

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
                }
            } else {
                gc2.setTime(dateFinal);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                    gc2.set(GregorianCalendar.MINUTE, 59);
                    gc2.set(GregorianCalendar.SECOND, 0);
                }
            }

            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();

            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }

        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }

        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }

        System.err.println("PESQUISANDO");
        return createQuery.getResultList();
    }

    public BigDecimal findValueIncomeGeneric(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType) {

        String sql = "Select sum(i.amount) from Income i ";
        if (procedure != null && procedure.getId() != null) {
            sql += " JOIN i.procedure p ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        sql += " where i.ativo = true and ";
        if (paymentType != null) {
            sql += " i.paymentType = :pt ";
        }
        if (procedure != null && procedure.getId() != null) {
            sql += " p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " d.id = : doctor ";
        }
        if (dateInitial != null) {
            sql += " i.createAt BETWEEN :dateInitial AND :dateFinal";
        }

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null && dateFinal != null) {
            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("paymentType", paymentType);
        }
        return (BigDecimal) createQuery.getSingleResult();

    }

    public Income diaryByIncome(Diary d) {
        try {
            String sql = "Select i from Income i JOIN i.diary d where d.id = :id and i.ativo = true";
            Query query = getEntityManager().createQuery(sql);
            query.setParameter("id", d.getId());

            return (Income) query.getSingleResult();
        } catch (NoResultException nr) {
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void reportIncome(List<Income> income) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();
        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, income, Reports.REPORT_INCOME);
    }

    public void reportIncomePrestacaoConta(List<Income> income) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();
        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, income, Reports.REPORT_PRESTACAO_CONTA);
    }

    public void reportIncomePrestacaoContaDoctor(List<DoctorConvenioIncome> list, String titulo) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();

        URL subreport = Thread.currentThread().getContextClassLoader().getResource(""
                + "br/com/foxline/clinicplus/reports");

        parametros.put("SUBREPORT_DIR", subreport.toString().replace("file:", ""));
        parametros.put("titulo", titulo);

        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, list, Reports.REPORT_FINANCEIRO_PRESTACAO_CONTA);
    }

    public void reportIncomeAll(List<Income> income, String titulo, BigDecimal... big) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();
        try {
            if (big != null && big[0] != null) {
                parametros.put("honorarios", "true");
                parametros.put("desconto", big[0]);
            }
        } catch (ArrayIndexOutOfBoundsException aioobe) {
        }

        parametros.put("titulo", titulo);
        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, income, Reports.REPORT_PRESTACAO_CONTA);
    }
    
    public void reportCaixaResumo(List<Income> income, String titulo, BigDecimal... big) {
        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();
        try {
            if (big != null && big[0] != null) {
                parametros.put("receitas", big[0] == null ? BigDecimal.ZERO : big[0]);
                parametros.put("despesas", big[1] == null ? BigDecimal.ZERO : big[1]);
                parametros.put("outrasreceitas", big[2] == null ? BigDecimal.ZERO : big[2]);
            }
        } catch (ArrayIndexOutOfBoundsException aioobe) {
        }

        parametros.put("titulo", titulo);
        parametros.put("usuarioprint", systemUserService.getCurrentUser().getName());
        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, income, Reports.CAIXA_RESUMO);
    }
    

    public void reportResumoCaixa(VoResumoCaixa voResumoCaixa, Date data) {

        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();

        URL subreport = Thread.currentThread().getContextClassLoader().getResource(""
                + "br/com/foxline/clinicplus/reports");

        parametros.put("SUBREPORT_DIR", subreport.toString().replace("file:", ""));
        parametros.put("data", data);

        List<VoResumoCaixa> vrcs = new ArrayList<VoResumoCaixa>();
        vrcs.add(voResumoCaixa);

        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, vrcs, Reports.REPORT_RESUMO_CAIXA);

    }

    public void reportIncomeGeral(List<Income> incomeEntrada, List<Income> incomeOutrasEntrada, List<Income> incomeSaida,
            BigDecimal receita, BigDecimal outrasReceitas, BigDecimal saida, BigDecimal saldoAnterior) {


        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();

        URL subreport = Thread.currentThread().getContextClassLoader().getResource(""
                + "br/com/foxline/clinicplus/reports");

        parametros.put("SUBREPORT_DIR", subreport.toString().replace("file:", ""));

        parametros.put("receitas", receita == null ? BigDecimal.ZERO : receita);
        parametros.put("outrasreceitas", outrasReceitas == null ? BigDecimal.ZERO : outrasReceitas);
        parametros.put("despesas", saida == null ? BigDecimal.ZERO : saida);
        parametros.put("saldoanterior", saldoAnterior == null ? BigDecimal.ZERO : saldoAnterior);

        List<VoPrestacaoConta> prestacaoContas = new ArrayList<VoPrestacaoConta>();

        VoPrestacaoConta vpc = new VoPrestacaoConta();

        vpc.setIncomesEntrada(incomeEntrada);
        vpc.setIncomesOutrasEntrada(incomeOutrasEntrada);
        vpc.setIncomesSaida(incomeSaida);

        prestacaoContas.add(vpc);

        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, prestacaoContas, Reports.REPORT_FINANCEIRO_GERAL);
    }

    /**
     * Médicos que atenderam nessa consulta
     *
     * @return
     */
    public List<Doctor> findIncomeDoctor(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType,
            SystemUser systemUser, TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure, GroupProcedure groupProcedure,
            Boolean compensado) {

        String sql = "Select DISTINCT(d) from Income i ";
        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN i.procedure p ";
        }
        //if (doctor != null && doctor.getId() != null) {
        sql += " JOIN i.doctor d ";
        //}
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }
        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }
        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }

        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }

        if (dateInitial != null) {
            sql += " and i.createAt BETWEEN :dateInitial AND :dateFinal";
        }

//        if (dateInitial != null) {
//            sql += " and ((i.createAt BETWEEN :dateInitial AND :dateFinal and i.faturamentoImediato = true )"
//                 + " or (i.compensado = true and i.dataCompensacao BETWEEN :dateInitial AND :dateFinal) "
//                 + " or (i.faturamentoImediato = false and (i.compensado = false or i.compensado is null )"
//                    + " and i.createAt BETWEEN :dateInitial AND :dateFinal)) ";
//        }
//
//       if(compensado != null && compensado){
//            sql += " and i.compensado = "+compensado+" ";
//        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }

        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        sql += " ORDER BY i.id ASC";

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);
            gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);


            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                gc2.set(GregorianCalendar.MINUTE, 59);
                gc2.set(GregorianCalendar.SECOND, 0);
            }

            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();

            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }

        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }

        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }

        return createQuery.getResultList();
    }

    /**
     * VALOR BRUTO
     */
    public BigDecimal getValueBrutoIncomeGeneric(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType, SystemUser systemUser,
            TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure, GroupProcedure groupProcedure, Boolean compensado) {

        String sql = "Select sum(i.amount) from Income i ";

        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || typeProcedure != null
                || groupProcedure != null) {
            sql += " JOIN i.procedure p ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }

        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }

        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }
        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }
//       if (dateInitial != null) {
//            sql += " and ((i.createAt BETWEEN :dateInitial AND :dateFinal and i.faturamentoImediato = true )"
//                 + " or (i.compensado = true and i.dataCompensacao BETWEEN :dateInitial AND :dateFinal) "
//                 + " or (i.faturamentoImediato = false and (i.compensado = false  or i.compensado is null ) and i.createAt BETWEEN :dateInitial AND :dateFinal)) ";
//        }
        if (dateInitial != null) {
            sql += " and ((i.createAt BETWEEN :dateInitial AND :dateFinal ))";
        }

        if (compensado != null && compensado) {
            sql += " and i.compensado = " + compensado + " ";
        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }
        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);

            if (gc1.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);
            }

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                    gc2.set(GregorianCalendar.MINUTE, 59);
                    gc2.set(GregorianCalendar.SECOND, 0);
                }
            }
            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();
            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }
        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }
        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }


        return (BigDecimal) createQuery.getSingleResult();
    }

    /**
     * COmpensacao de convenio
     *
     * @param income
     * @param procedure
     * @param doctor
     * @param dateInitial
     * @param dateFinal
     * @param paymentType
     * @param systemUser
     * @param typeIncome
     * @param typeDebt
     * @param procedures
     * @param operator
     * @param operators
     * @param typeCredit
     * @param typeProcedure
     * @param groupProcedure
     * @param compensado
     * @return
     */
    public List<Income> findIncomeGenericCompensacao(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType,
            SystemUser systemUser, TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure,
            GroupProcedure groupProcedure, Boolean compensado, Date dateCompensado) {

        String sql = "Select i from Income i ";
        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || (typeProcedure != null)
                || (groupProcedure != null)) {
            sql += " JOIN i.procedure p ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }
        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }
        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }

        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }

        if (dateInitial != null) {
            sql += " and ((i.createAt BETWEEN :dateInitial AND :dateFinal))";
        }

        if (compensado != null && compensado) {
            sql += " and i.compensado = " + compensado + " ";
        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (compensado != null && compensado && dateCompensado != null) {
            sql += " and i.dataCompensacao BETWEEN :dateInitialCompensacao AND :dateFinalCompensacao ";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }

        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        sql += " ORDER BY i.createAt ASC";

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);

            if (gc1.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);
            }

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
                }
            } else {
                gc2.setTime(dateFinal);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                    gc2.set(GregorianCalendar.MINUTE, 59);
                    gc2.set(GregorianCalendar.SECOND, 0);
                }
            }

            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();

            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }

        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }

        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }

        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }

        if (dateCompensado != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateCompensado);
            gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);

            Calendar gc2 = GregorianCalendar.getInstance();
            gc2.setTime(dateCompensado);
            gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
            gc2.set(GregorianCalendar.MINUTE, 59);
            gc2.set(GregorianCalendar.SECOND, 59);

            createQuery.setParameter("dateInitialCompensacao", gc1.getTime());
            createQuery.setParameter("dateFinalCompensacao", gc2.getTime());
        }

        return createQuery.getResultList();
    }

    /**
     * COMPENSACAO
     *
     * @param income
     * @param procedure
     * @param doctor
     * @param dateInitial
     * @param dateFinal
     * @param paymentType
     * @param systemUser
     * @param typeIncome
     * @param typeDebt
     * @param procedures
     * @param operator
     * @param operators
     * @param typeCredit
     * @param typeProcedure
     * @param groupProcedure
     * @param compensado
     * @return
     */
    public BigDecimal getValueIncomeGenericCompensacao(Income income, Procedure procedure,
            Doctor doctor, Date dateInitial, Date dateFinal, PaymentType paymentType, SystemUser systemUser,
            TypeIncome typeIncome, TypeDebt typeDebt, List<Procedure> procedures,
            Operator operator, List<Operator> operators, TypeCredit typeCredit, TypeProcedure typeProcedure,
            GroupProcedure groupProcedure, Boolean compensado, boolean sumAmountPaidOrAmount, Date dateCompensado) {


        String sql = "Select";

        if (sumAmountPaidOrAmount) {
            sql += " sum(i.amountPaid) ";
        } else {
            sql += " sum(i.totalAmount) ";
        }

        sql += " from Income i ";

        if (((procedure != null && procedure.getId() != null)
                || (procedures != null && !procedures.isEmpty()))
                || typeProcedure != null
                || groupProcedure != null) {
            sql += " JOIN i.procedure p ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " JOIN i.doctor d ";
        }
        if (systemUser != null && systemUser.getId() != null) {
            sql += " JOIN i.systemUser su ";
        }
        if ((operator != null && operator.getId() != null) || (operators != null && !operators.isEmpty())) {
            sql += " JOIN i.diary di JOIN di.operator op ";
        }

        sql += " where i.ativo = true ";
        if (operator != null && operator.getId() != null) {
            sql += " and op.id = :operator ";
        }

        if (operators != null && !operators.isEmpty()) {
            sql += " and op.id IN :operators ";
        }

        if (procedures != null && !procedures.isEmpty()) {
            sql += " and p.id IN :procedures";
        }
        if (paymentType != null) {
            sql += " and i.paymentType = :pt";
        }
        if (procedure != null && procedure.getId() != null) {
            sql += " and p.id = :procedure ";
        }
        if (doctor != null && doctor.getId() != null) {
            sql += " and d.id = :doctor ";
        }
        if (dateInitial != null) {
            sql += " and ((i.createAt BETWEEN :dateInitial AND :dateFinal))";
        }

        if (compensado != null && compensado) {
            sql += " and ( i.compensado = " + compensado + " )";
        }

        if (compensado != null && !compensado) {
            sql += " and (i.compensado = " + compensado + " or i.compensado is null ) ";
        }

        if (compensado != null && compensado && dateCompensado != null) {
            sql += " and i.dataCompensacao BETWEEN :dateInitialCompensacao AND :dateFinalCompensacao ";
        }

        if (systemUser != null && systemUser.getId() != null) {
            sql += " and su.id = :systemuser";
        }
        if (typeIncome != null) {
            if (typeIncome == TypeIncome.C) {
                sql += " and ( i.typeIncome = :typeIncome or i.typeIncome is null) ";
            } else {
                sql += " and i.typeIncome = :typeIncome";
            }
        }
        if (typeDebt != null) {
            sql += " and i.typeDebt = :typeDebt";
        }

        if (typeCredit != null) {
            sql += " and i.typeCredit = :typeCredit";
        }
        if (typeProcedure != null) {
            sql += " and p.typeProcedure = :typeProcedure";
        }
        if (groupProcedure != null) {
            sql += " and p.groupProcedure = :groupProcedure";
        }

        Query createQuery = getEntityManager().createQuery(sql);

        if (procedure != null && procedure.getId() != null) {
            createQuery.setParameter("procedure", procedure.getId());
        }
        if (doctor != null && doctor.getId() != null) {
            createQuery.setParameter("doctor", doctor.getId());
        }
        if (dateInitial != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateInitial);

            if (gc1.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);
            }

            Calendar gc2 = GregorianCalendar.getInstance();
            if (dateFinal == null) {
                gc2.setTime(dateInitial);
                gc2.add(GregorianCalendar.DAY_OF_MONTH, 1);
                gc2.set(GregorianCalendar.HOUR_OF_DAY, 0);
            } else {
                gc2.setTime(dateFinal);
                if (gc2.get(GregorianCalendar.HOUR_OF_DAY) == 0) {
                    gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
                    gc2.set(GregorianCalendar.MINUTE, 59);
                    gc2.set(GregorianCalendar.SECOND, 0);
                }
            }
            dateInitial = gc1.getTime();
            dateFinal = gc2.getTime();
            createQuery.setParameter("dateInitial", dateInitial);
            createQuery.setParameter("dateFinal", dateFinal);
        }
        if (paymentType != null) {
            createQuery.setParameter("pt", paymentType);
        }
        if (systemUser != null && systemUser.getId() != null) {
            createQuery.setParameter("systemuser", systemUser.getId());
        }
        if (typeIncome != null) {
            createQuery.setParameter("typeIncome", typeIncome);
        }
        if (typeDebt != null) {
            createQuery.setParameter("typeDebt", typeDebt);
        }
        if (typeCredit != null) {
            createQuery.setParameter("typeCredit", typeCredit);
        }
        if (typeProcedure != null) {
            createQuery.setParameter("typeProcedure", typeProcedure);
        }
        if (groupProcedure != null) {
            createQuery.setParameter("groupProcedure", groupProcedure);
        }
        if (operators != null && !operators.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Operator operator1 : operators) {
                list.add(operator1.getId());
            }
            createQuery.setParameter("operators", list);
        }
        if (procedures != null && !procedures.isEmpty()) {
            List<Long> list = new ArrayList<Long>();
            for (Procedure procedure1 : procedures) {
                list.add(procedure1.getId());
            }
            createQuery.setParameter("procedures", list);
        }
        if (operator != null && operator.getId() != null) {
            createQuery.setParameter("operator", operator.getId());
        }

        if (dateCompensado != null) {
            Calendar gc1 = GregorianCalendar.getInstance();
            gc1.setTime(dateCompensado);
            gc1.set(GregorianCalendar.HOUR_OF_DAY, 0);

            Calendar gc2 = GregorianCalendar.getInstance();
            gc2.setTime(dateCompensado);
            gc2.set(GregorianCalendar.HOUR_OF_DAY, 23);
            gc2.set(GregorianCalendar.MINUTE, 59);
            gc2.set(GregorianCalendar.SECOND, 59);

            createQuery.setParameter("dateInitialCompensacao", gc1.getTime());
            createQuery.setParameter("dateFinalCompensacao", gc2.getTime());
        }

        return (BigDecimal) createQuery.getSingleResult();
    }

    public void dre(List<VoDreDespesas> voDreDespesas, List<VoDreReceitas> voDreReceitas, BigDecimal geralReceita, BigDecimal geralDespesa) {

        HashMap<String, Object> parametros;
        parametros = new HashMap<String, Object>();

        URL subreport = Thread.currentThread().getContextClassLoader().getResource(""
                + "br/com/foxline/clinicplus/reports");

        parametros.put("SUBREPORT_DIR", subreport.toString().replace("file:", ""));
        parametros.put("receitas", voDreReceitas);
        parametros.put("despesas", voDreDespesas);

        VoDre voDre = new VoDre(voDreReceitas, voDreDespesas, geralReceita, geralDespesa);
        List<VoDre> list = new ArrayList<VoDre>();
        list.add(voDre);

        parametros = clinicService.getCabecalho(parametros);
        ReportService.genReport(parametros, list, Reports.DRE);

    }

    /**
     * Retorna as entradas que não tem procedimento nem convenio nem paciente
     *
     * @return
     */
    public List<Income> problema() {
        String str = "Select distinct(i) from Income i where i.doctor is null or i.procedure is null or i.systemUser is null order by i.id desc";
        Query query = getEntityManager().createQuery(str);
        
        return query.getResultList();
    }
}
